MySQL数据库备份

2023-01-08 02:21| 来源: 网络整理| 查看: 265

一、mysqldump数据库备份工具

mysqldump是MySQL自带的备份工具，用于执行逻辑备份。它可以备份一个或多个数据库，默认生成一个可以被执行的SQL语句集合。也可以生成CSV、其他分割文本、或者XML格式的输出。

1、mysqldump语法

以下是三种常见的使用mysqldump的语法，备份一个或多个表、备份一个或多给完整的库，或者备份整个MySQL服务器。

shell> mysqldump [options] db_name [tbl_name …] shell> mysqldump [options] --databases db_name … shell> mysqldump [options] --all-databases

如果要备份整个数据库，在db_name后不要加任何表名，或者使用–database或–all-databases选项。

筛选选项：

–all-databases , -A：备份所有数据库中的所有表。–databases , -B：备份几个数据库。通常，mysqldump将命令行上的第一个名称参数视为数据库名称，将后续名称视为表名称。使用此选项，它将所有名称参数视为数据库名称。CREATE DATABASE和USE语句包含在每个新数据库之前的输出中。此选项可用于备份INFORMATION_SCHEMA和performance_schema数据库，即使使用该–all-databases选项，通常也不会备份这两个表。–ignore-table= db_name.tbl_name：不要备份给定的表，必须使用数据库和表名来指定该表。要忽略多个表，请多次使用此选项。此选项也可用于忽略视图。–no-data ， -d：不要写任何表行信息（即不要备份表内容）。如果要仅备份CREATE TABLE表的语句（例如，通过加载备份文件来创建表的空副本），这将非常有用。–events , -E：在输出中包含备份数据库的事件计划程序事件。此选项需要这些数据库的EVENT权限。–routines ， -R：在输出中包含备份数据库的存储例程（过程和函数）。此选项需要表mysql.proc的SELECT权限。–triggers：在输出中包含每个备份表的触发器。默认情况下启用此选项; 使用–skip-triggers禁用。–where=’ where_condition ’ ， -w ’ where_condition ’仅备份由给定WHERE条件选择的行。如果条件包含空格或其他对命令解释程序特殊的字符，则必须引用该条件。例子：–where=“user=‘jimf’” -w"userid>1" -w"userid/data/backup/tgz/mysqlbak1.tar.gz"

可用参数：

--parallel=4：加速备份，这个选项会指定xtrabackup备份文件的线程数。

提取tar流，需要加i参数。

备份过程中，需要输入远程主机密码：

如果不想输入密码，需要设置远程主机的免密登录，远程登录就不需要输入密码了。

7、xtrabackup单表导出和导入

单表备份：

innobackupex -uroot -pmagedu --include='hellodb.students' /backup

备份表结构：

mysql -e 'show create table hellodb.students' >student.sql

删除表：

mysql -e 'drop table hellodb.students'

导出：

innobackupex --apply-log --export /backups/2018-02-23_15-03-23/

创建表：

mysql>CREATE TABLE `students` ( `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum('F','M') NOT NULL, `ClassID` tinyint(3) unsigned DEFAULT NULL, `TeacherID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`) ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8

删除表空间：

alter table students discard tablespace;

导入：

cp /backups/2018-02-23_15-03-23/hellodb/students.{cfg,exp,ibd} /var/lib/mysql/hellodb/ chown -R mysql.mysql /var/lib/mysql/hellodb/ mysql>alter table students import tablespace; 8、mysql定时备份恢复脚本案例

仅仅对mysql的cmsdb库进行备份命令：

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --databases=cmsdb --stream=tar /data/back_data/ 2>/data/back_data/cmsdb.log | gzip >/data/back_data/cmsdb.tar.gz

说明：

--database=cmsdb ：单独对cmsdb数据库做备份，若是不添加此参数那就那就是对全库做备份2>/data/back_data/cmsdb.log 输出信息写入日志中gzip >/data/back_data/cmsdb.tar.gz 打包压缩存储到该文件中

此处可以写个脚本做备份(backup.sh)：

#!/bin/sh echo "开始备份..."`date` log=cmsdb_`date +%y%m%d%H%M`.log str=cmsdb_`date +%y%m%d%H%M`.tar.gz innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --database=cmsdb --stream=tar /data/back_data/ 2>/data/back_data/$log | gzip >/data/back_data/$str echo "备份完毕..."`date`

恢复数据：

先停止数据库：

service mysqld stop

解压

tar -izxvf cmsdb.tar.gz -C /data/back_data/db/

没有db，需要：

mkdir /data/back_data/db/

恢复：

innobackupex --defaults-file=/etc/my.cnf --user=root --password --apply-log /data/back_data/db/ innobackupex --defaults-file=/etc/my.cnf --user=root --password --copy-back /data/back_data/db/

赋权：

chown -R mysql.mysql /var/lib/mysql/*

重启数据库：

service mysqld restart

【本文地址】

公司简介

联系我们